package Database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import Model.Committee;
import Model.CommitteeRole;

public class CommitteeRoleDB {
	public static void createCommitteeRole(CommitteeRole role) {
		Connection con = DBConnection.connect(true);
		PreparedStatement stmt = null;
		String select = "INSERT INTO committeerole(CommitteeRole_ID, CommitteeRole_Name, Committee_ID, StaffNames) VALUES(?, ?, ?, ?)";

		try {
			stmt = con.prepareStatement(select);
			stmt.setString(1, role.getId());
			stmt.setString(2, role.getName());
			stmt.setString(3, role.getCommittee_id());
			stmt.setString(4, role.getStaffName());

			stmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
			}
		}
	}

	public static void updateCommitteeRole(CommitteeRole role) {
		Connection con = DBConnection.connect(true);
		PreparedStatement stmt = null;
		String select = "UPDATE committeerole SET CommitteeRole_Name = ?, StaffNames = ? WHERE CommitteeRole_ID = ? AND Committee_ID = ?";

		try {
			stmt = con.prepareStatement(select);
			stmt.setString(1, role.getName());
			stmt.setString(2, role.getStaffName());
			stmt.setString(3, role.getId());
			stmt.setString(4, role.getCommittee_id());

			stmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
			}
		}
	}

	public static void deleteCommitteeRole(String id) {
		Connection con = DBConnection.connect(true);
		PreparedStatement stmt = null;
		String select = "DELETE FROM committeerole WHERE Committee_ID = ?";

		try {
			stmt = con.prepareStatement(select);
			stmt.setString(1, id);

			stmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
			}
		}
	}

	public static ArrayList<CommitteeRole> getCommitteeRole(String committeeId) {
		Connection con = DBConnection.connect(true);
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String select = "SELECT * FROM committeerole WHERE Committee_ID = ? ";

		ArrayList<CommitteeRole> committeeRoleArr = new ArrayList<CommitteeRole>();
		CommitteeRole role = null;

		try {
			stmt = con.prepareStatement(select);
			stmt.setString(1, committeeId);
			rs = stmt.executeQuery();
			while (rs.next()) {
				role = new CommitteeRole();
				role.setId(rs.getString("CommitteeRole_ID"));
				role.setName(rs.getString("CommitteeRole_Name"));
				role.setCommittee_id(rs.getString("Committee_ID"));
				role.setStaffName(rs.getString("StaffNames"));

				committeeRoleArr.add(role);
			}
		} catch (Exception e) {
			e.printStackTrace();
			role = null;
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
			}
		}

		return committeeRoleArr;
	}
	
	public static ArrayList<CommitteeRole> getAllCommitteeRole() {
		Connection con = DBConnection.connect(true);
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String select = "SELECT * FROM committeerole";

		ArrayList<CommitteeRole> committeeRoleArr = new ArrayList<CommitteeRole>();
		CommitteeRole role = null;

		try {
			stmt = con.prepareStatement(select);
			rs = stmt.executeQuery();
			while (rs.next()) {
				role = new CommitteeRole();
				role.setId(rs.getString("CommitteeRole_ID"));
				role.setName(rs.getString("CommitteeRole_Name"));
				role.setCommittee_id(rs.getString("Committee_ID"));
				role.setStaffName(rs.getString("StaffNames"));

				committeeRoleArr.add(role);
			}
		} catch (Exception e) {
			e.printStackTrace();
			role = null;
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
			}
		}

		return committeeRoleArr;
	}

	public static void appointStaffRole(String staffNames, String id,
			String name) {
		Connection con = DBConnection.connect(true);
		PreparedStatement stmt = null;
		String select = "UPDATE committeerole SET StaffNames = ? WHERE Committee_ID = ? AND CommitteeRole_Name = ?";

		try {
			stmt = con.prepareStatement(select);
			stmt.setString(1, staffNames);
			stmt.setString(2, id);
			stmt.setString(3, name);
			stmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
			}
		}
	}

	public static String retrieveStaffName(String id, String name) {
		Connection con = DBConnection.connect(true);
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String select = "SELECT StaffNames FROM committeerole WHERE Committee_ID = ? AND CommitteeRole_Name = ?";
		String staffname = null;

		try {
			stmt = con.prepareStatement(select);
			stmt.setString(1, id);
			stmt.setString(2, name);
			rs = stmt.executeQuery();
			while (rs.next()) {
				staffname = rs.getString("StaffNames");
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
			}
		}

		return staffname;
	}
}
